/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.dusin.commonutil;

import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;

/**
 *
 * @author tengyu
 */
public class DatabaseUtil {

    static Logger logger = Logger.getLogger(DatabaseUtil.class);

    private static String getParamsHolder(int n) {
        StringBuilder sb = new StringBuilder();  //根据n，拼凑参数占位符，如n=3 ，则字符串为 (?,?,?);
        sb.append("(");
        for (int i = 0; i < n; i++) {
            sb.append("?,");
        }
        sb.deleteCharAt(sb.length() - 1);
        sb.append(")");
        return sb.toString();
    }

    public static String getFtSizeSelectStr(String ft, int n, String cond) {
        return "select count(*) from " + ft + getParamsHolder(n) + "  where " + cond;
    }

    //拼凑从表值函数中获取数据的select语句
    public static String getSimpleFtSelectStr(String ft, int n, String cond, String orderString) {
        return "select *  from " + ft + getParamsHolder(n) + " where " + cond + orderString;
    }

    /**
     * 拼凑从表值函数中获取数据的分页select语句
     *
     * @param ft 表值函数名
     * @param n 参数个数
     * @param cond 选择条件
     * @return
     */
    public static String getFtSelectStr(String ft, int n, String cond, String orderField) {
        return "select * from (select * ,row_number() over (order by " + orderField + ") rn from " + ft + getParamsHolder(n) + " where " + cond + ") t where rn>? and rn<=?";
    }

    //获取一般视图分页Select 语句
    public static String getViewSizeSelectStr(String view, String cond, String orderField) {
        return "select count(*) from " + view + " where " + cond;
    }

    public static String getViewSelectStr(String ft, String cond) {
        return "select * from (select * ,row_number() over (order by id) rn from " + ft + "where" + cond + ") t where rn>? and rn<=?";
    }

    public static String getInsertSqlString(String tableName, String fieldNames[], int[] selectedIndexes) {
        StringBuilder sb = new StringBuilder();
        sb.append("insert into ").append(tableName).append("(");
        for (int i : selectedIndexes) {
            sb.append(fieldNames[i]).append(",");
        }
        sb.deleteCharAt(sb.length() - 1);
        sb.append(") values(");
        for (int i : selectedIndexes) {
            sb.append("?,");
        }
        sb.deleteCharAt(sb.length() - 1);
        sb.append(")");
        return sb.toString();
    }

    /**
     *
     * @param tableName
     * @param fieldNames
     * @param start 开始位置
     * @param length 长度
     * @return
     */
    public static String getInsertSqlString(String tableName, String fieldNames[], int start, int length) {
        StringBuilder sb = new StringBuilder();
        sb.append("insert into ").append(tableName).append("(");
        for (int i = start; i < start + length; i++) {
            sb.append(fieldNames[i]).append(",");
        }
        sb.deleteCharAt(sb.length() - 1);
        sb.append(") values(");
        for (int i = start; i < start + length; i++) {
            sb.append("?,");
        }
        sb.deleteCharAt(sb.length() - 1);
        sb.append(")");
        return sb.toString();
    }

    //没有where条件的update语句
    public static String getUpdateSqlString(String tableName, String fieldNames[], int[] selectedIndexes) {
        StringBuilder sb = new StringBuilder();
        sb.append("update ").append(tableName).append(" set ");
        for (int i : selectedIndexes) {
            sb.append(fieldNames[i]).append("=?,");
        }
        sb.deleteCharAt(sb.length() - 1);
        return sb.toString();
    }

    public static String getUpdateSqlString(String tableName, String fieldNames[], int[] selectedIndexes, int keyIndex) {
        StringBuilder sb = new StringBuilder();
        sb.append("update ").append(tableName).append(" set ");
        for (int i : selectedIndexes) {
            sb.append(fieldNames[i]).append("=?,");
        }
        sb.deleteCharAt(sb.length() - 1);
        sb.append(" where ");
        sb.append(fieldNames[keyIndex]).append("=?");
        return sb.toString();
    }

    /**
     *
     * @param tableName
     * @param fieldNames
     * @param start
     * @param length
     * @param keyIndex 关键码所在的索引
     * @return
     */
    public static String getUpdateSqlString(String tableName, String fieldNames[], int start, int length, int keyIndex) {
        StringBuilder sb = new StringBuilder();
        sb.append("update ").append(tableName).append(" set ");
        for (int i = start; i < start + length; i++) {
            sb.append(fieldNames[i]).append("=?,");
        }
        sb.deleteCharAt(sb.length() - 1);
        sb.append(" where ");

        sb.append(fieldNames[keyIndex]).append("=?");
        return sb.toString();
    }

    public static String getSelectSizeString(String tableName) {
        StringBuilder sb = new StringBuilder();
        sb.append("select count(*) from ").append(tableName);
        return sb.toString();
    }

    public static String getSelectSizeByOrgCodeString(String tableName) {
        StringBuilder sb = new StringBuilder();
        sb.append(getSelectSizeString(tableName)).append("where orgCode=?");
        return sb.toString();
    }

    public static String getSimpleSelectAllString(String tableName,
            String fieldNames[], int start, int length) {
        StringBuilder sb = new StringBuilder();
        sb.append("select ");
        for (int i = start; i < start + length; i++) {
            sb.append(fieldNames[i]).append(",");
        }
        sb.deleteCharAt(sb.length() - 1);
        sb.append(" from ").append(tableName);
        return sb.toString();
    }

    public static String getSimpleSelectByOrgCode(String tableName,
            String fieldNames[], int start, int length,
            String orgCode) {
        StringBuilder sb = new StringBuilder();
        sb.append(getSimpleSelectAllString(tableName, fieldNames, start, length));
        sb.append(" where ");
        sb.append("orgCode like '")
                .append(orgCode).append("%'");
        return sb.toString();
    }

    public static String getSimpleSelectByIdString(String tableName,
            String fieldNames[], int start, int length,
            long id) {
        StringBuilder sb = new StringBuilder();
        sb.append(getSimpleSelectAllString(tableName, fieldNames, start, length));
        sb.append(" where ");
        sb.append(" id=")
                .append(id);
        return sb.toString();
    }

    public static String getSelectedItemsString(String[] fieldNames, int[] selectedIndices) {
        StringBuilder sb = new StringBuilder();
        for (int i : selectedIndices) {
            sb.append(fieldNames[i]).append(",");
        }
        sb.deleteCharAt(sb.length() - 1);
        return sb.toString();
    }

    public static String getSelectedItemsString(String[] fieldNames, int start, int length) {
        StringBuilder sb = new StringBuilder();
        for (int i = start; i < start + length; i++) {
            sb.append(fieldNames[i]).append(",");
        }
        sb.deleteCharAt(sb.length() - 1);
        return sb.toString();
    }

    /**
     * 将表名加到字段名前，如t_org.id
     *
     * @param tableName
     * @param fieldNames
     * @param start
     * @param length
     * @return
     */
    public static String getSelectedItemsString(String tableName, String[] fieldNames, int[] indices) {
        StringBuilder sb = new StringBuilder();
        for (int i : indices) {
            sb.append(tableName).append(".").append(fieldNames[i]).append(",");
        }
        sb.deleteCharAt(sb.length() - 1);
        return sb.toString();
    }

    public static String getSelectedItemsString(String tableName, String[] fieldNames, int start, int length) {
        StringBuilder sb = new StringBuilder();
        for (int i = start; i < start + length; i++) {
            sb.append(tableName).append(".").append(fieldNames[i]).append(",");
        }
        sb.deleteCharAt(sb.length() - 1);
        return sb.toString();
    }

    public static String getSimplePageSelectAllString(String tableName, String[] fieldNames, int start, int length) {
        StringBuilder sb = new StringBuilder();
        sb.append("select ")
                .append(getSelectedItemsString(fieldNames, start, length))
                .append(" from ")
                .append("(select ")
                .append(getSelectedItemsString(tableName, fieldNames, start, length))
                .append(",row_number() over (order by id) rn from ")
                .append(tableName)
                .append(") t ");

        return sb.toString();
    }

    public static String getSimplePageSelectAllString(String tableName, String[] fieldNames, int start, int length, String condition) {
        StringBuilder sb = new StringBuilder();
        sb.append("select ")
                .append(getSelectedItemsString(fieldNames, start, length))
                .append(" from ")
                .append("(select ")
                .append(getSelectedItemsString(tableName, fieldNames, start, length))
                .append(",row_number() over (order by id) rn from ")
                .append(tableName).append(" ").append(condition)
                .append(") t ");

        return sb.toString();
    }

    public static String getPageSelectAllString(String tableName,
            String[] fieldNames, int start, int length,
            String joinTableName, String[] joinFieldNames, int[] joinSelectedIndices,
            String joinId) {
        StringBuilder sb = new StringBuilder();
        sb.append("select ")
                .append(getSelectedItemsString(fieldNames, start, length))
                .append(",")
                .append(getSelectedItemsString(joinFieldNames, joinSelectedIndices))
                .append(" from ")
                .append("(select ")
                .append(getSelectedItemsString(tableName, fieldNames, start, length))
                .append(",")
                .append(getSelectedItemsString(joinTableName, joinFieldNames, joinSelectedIndices))
                .append(",row_number() over (order by ").append(tableName).append(".id) rn from ")
                .append(tableName)
                .append(" left join ")
                .append(joinTableName)
                .append(" on ")
                .append(tableName)
                .append(".")
                .append(joinId)
                .append("=")
                .append(joinTableName).append(".").append(joinId)
                .append(") t ");

        return sb.toString();
    }

    public static String getSimpleSelectByMenuCode(String tableName, String[] FieldNames, int i, int i0, String menuCode) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    public static int findAllSize(JdbcTemplate jdbcTemplate, String view, String condition, Object... args) {
        String sqlSize = "select count(*) from " + view + " " + condition;
        logger.debug(sqlSize);
        int size = jdbcTemplate.queryForObject(sqlSize, Integer.class, args);
        return size;
    }

    public static List<Map<String, Object>> findAllByView(JdbcTemplate jdbcTemplate, String view, String orderByString, String condition, Object... args) {
        StringBuilder sb = new StringBuilder();
        sb.append("select * from (select * ,row_number() over (order by ").append(orderByString).append(") rn from ")
                .append(view).append(" ").append(condition).append(") t").append(" where rn>=? and rn<=?");
        logger.debug(sb.toString());
        return jdbcTemplate.queryForList(sb.toString(), args);
    }

    public static boolean deleteAllInOrgCode(JdbcTemplate jdbcTemplate, String tableName, String orgCode) {
        String sql = "delete from " + tableName + " where orgCode like ?";
        int rows = jdbcTemplate.update(sql, orgCode + "%");
        if (rows <= 0) {
            return false;
        } else {
            return true;
        }        
    }
    
    public static boolean deleteRecords(JdbcTemplate jdbcTemplate, String tableName, String condition) {
        String sql = "delete from " + tableName+" " + condition;
        int rows = jdbcTemplate.update(sql);
        if (rows <= 0) {
            return false;
        } else {
            return true;
        }        
    }
    
    
    /**
     * 将List中的map的某个键值连接成逗号分隔的字符串
     * @param list
     * @param idName
     * @return 
     */
    public static String joinFromMapList(List<Map<String,Object>> list,String idName){
        StringBuilder sb = new StringBuilder();
        for(Map map:list){
            sb.append(map.get(idName)).append(",");
        }
        if(sb.length()>0&&sb.toString().endsWith(",")){
            sb.deleteCharAt(sb.length()-1);
        }
        return sb.toString();
    }
    
    public static List<Map<String,Object>> findAll(JdbcTemplate jdbcTemplate,String tableName,String fields,String condition,Object... args) {
        String sql = "select "+fields+" from "+tableName+" "+condition;
        logger.debug(sql);
        List<Map<String,Object>> list = jdbcTemplate.queryForList(sql,args);
        return list;        
    }
    
//     public static List<Map<String,Object>> findAllByView(JdbcTemplate jdbcTemplate,String view ,String condition,Object... args){
//        string sql = "select * from "+view + condition + 
//        sb.append("select * from (select * ,row_number() over (order by ").append(orderByString).append(") rn from ")
//                .append(view).append(" ").append(condition).append(") t").append(" where rn>=? and rn<=?");        
//        logger.debug(sb.toString());
//        return jdbcTemplate.queryForList(sb.toString(),args);
//     }
    
    /**
     * 
     * @param valueMap   需要修改的新值
     * @param condition  where 查询条件
     */
    public static String getBatchUpdateSQL (String table,Map<String,String> valueMap,String condition){
        StringBuilder sb = new StringBuilder();
        sb.append("update ").append(table).append(" set ");
        for(String key :valueMap.keySet()){
            String value = valueMap.get(key);
            sb.append(key).append("=").append("'").append(value).append("'");
            sb.append(",");
        }
        if(sb.length()>0&&sb.toString().endsWith(",")){
            sb.deleteCharAt(sb.length()-1);
        }
        sb.append(" ").append(condition);
        return sb.toString();
    }
}
